import json

import xlrd
from flask import (
    render_template, jsonify, request, session, Response
)

from blues.cmdb import bp
from config import ROLE_ADMIN
from database import exec_sql, exec_sql_f
from model.cmdb_cloud_resource import CloudResource
from model.cmdb_code import Code
from model.cmdb_db import Db
from model.cmdb_host import Host
from model.cmdb_kv import Kv
from model.cmdb_lb import Loadbalancer
from model.cmdb_os import Os
from model.cmdb_svc import Svc
from public.time_utils import getNowTS


# 渲染web页面
@bp.route('/cmdb/<type>')
def cmdb_home(type):
    keyword = 'cmdb_' + type
    if type == 'cloud_resource':
        keyword = 'cloud_resource'

    return render_template('cmdb_' + type + '.html', active=keyword)


# cmdb资源搜索API
@bp.route('/api/v1/cmdb/<type>')
def api_resource_all(type):
    # 根据type类型字段，判断返回哪一个模型类
    if type == 'host':
        t = Host()
    elif type == 'db':
        t = Db()
    elif type == 'lb':
        t = Loadbalancer()
    elif type == 'os':
        t = Os()
    elif type == 'kv':
        t = Kv()
    elif type == 'svc':
        t = Svc()
    elif type == 'code':
        t = Code()
    elif type == 'cloud_resource':
        t = CloudResource()
    result = t.find_all()

    # 根据权限处理特殊字段
    if session.get('role') not in ROLE_ADMIN:
        for i in result:
            i['ssh_password'] = "***"
            i['password'] = "***"
            i['accesskey'] = "***"
            i['secret'] = "***"

    # todo: 临时处理，将云资源重要信息隐藏
    if type == 'cloud_resource':
        # instance_id = Column(String(255))  # 实例ID or bucket
        # ip_private = Column(String(255))  # 内网IP
        # ip_public = Column(String(255))  # 公网IP
        for i in result:
            i['org'] = i['org'][:-2] + "***"
            i['project'] = "**" +i['project'][2:-2] + "**"
            i['instance_id'] = i['instance_id'][:-12] + "***"
            if i['ip_private']:
                i['ip_private'] = i['ip_private'][:-8] + "***"
            if i['ip_public']:
                i['ip_public'] = i['ip_public'][:-5] + "***"
            if i['res_function']:
                i['res_function'] = "**" + i['res_function'][2:-2] + "**"
            if i['comment']:
                i['comment'] = "**" + i['comment'][2:-2] + "**"

    return jsonify({"code": 200, "data": result})


# 文件上传接口
@bp.route('/api/v1/cmdb/upload', methods=['POST'])
def save_upload_file():
    obj = request.files.get('file')
    print("接受上传文件：" + obj.filename)
    new_file = './upload/' + getNowTS() + '_' + session['username'] + '_' + obj.filename
    print("保存为文件：" + new_file)
    obj.save(new_file)

    # 解析excel文件，获取sheet表对象
    try:
        work_book = xlrd.open_workbook(new_file)
        sheets_name_li: list = work_book.sheet_names()
        print("文件表单列表：" + str(sheets_name_li))
    except Exception as e:
        print(e)
        return jsonify({'code': 500, 'error': str(e)})

    return jsonify({'code': 200, 'sheets_name': sheets_name_li, 'filename': new_file})


# 全局搜索接口
@bp.route('/api/v1/cmdb/search', methods=['POST'])
def global_search():
    keyword = request.form.get('keyword')
    print("全局搜索关键词：" + keyword)
    d = {}
    sql1 = "SELECT COUNT(*) FROM {table} WHERE {field1} LIKE '%{keyword}%'"
    sql2 = "SELECT COUNT(*) FROM {table} WHERE {field1} LIKE '%{keyword}%' OR {field2} LIKE '%{keyword}%'"

    d['host'] = exec_sql_f(sql2.format(table='cmdb_host', keyword=keyword, field1='innerip', field2='outerip'))
    d['db'] = exec_sql_f(sql2.format(table='cmdb_db', keyword=keyword, field1='innerip', field2='inneraddr'))
    d['lb'] = exec_sql_f(sql1.format(table='cmdb_lb', keyword=keyword, field1='ip'))
    d['os'] = exec_sql_f(sql1.format(table='cmdb_os', keyword=keyword, field1='bucket'))
    d['kv'] = exec_sql_f(sql2.format(table='cmdb_kv', keyword=keyword, field1='innerip', field2='inneraddr'))
    d['svc'] = exec_sql_f(sql2.format(table='cmdb_svc', keyword=keyword, field1='inneraddr', field2='name'))
    d['code'] = exec_sql_f(sql2.format(table='cmdb_code', keyword=keyword, field1='job', field2='git'))

    d['cloud_resource'] = exec_sql_f(sql2.format(table='cmdb_code', keyword=keyword, field1='job', field2='git'))

    print("搜索结果：" + str(d))
    return jsonify(d)


# 导入更新接口
@bp.route('/api/v1/cmdb/update', methods=['POST'])
def update():
    type = request.form.get('type')  # type字段决定了具体更新到那张表(cmdb_%s)
    filename = request.form.get('filename')
    sheet_name = request.form.get('sheet_name')
    print("开始更新cmdb：", type, filename, sheet_name)

    # 开始使用excel更新数据库
    work_book = xlrd.open_workbook(filename)
    sheet = work_book.sheet_by_name(sheet_name)

    # 读取表头行（excel模板第3行）
    th_li = sheet.row_values(2)
    th_li.pop(0)  # 删除左边第一列
    fields_sql = str(th_li).lstrip("[").rstrip("]").replace('\'', '`')  # 表头转换为sql中的field字段

    # 读取表数据行（excel模板第4行开始）
    tr_li = []
    for i in range(3, sheet.nrows):
        tr_li.append(sheet.row_values(i))
    print(th_li)

    # 初始化导入结果变量
    insert_count = 0
    change_count = 0
    ignore_count = 0

    # 遍历每一行数据，i为行，j为列，生成sql语句并执行
    for i in range(0, len(tr_li)):
        tr_li[i].pop(0)
        values_li = []
        for j in range(len(th_li)):
            values_li.append(tr_li[i][j])
        # 组装sql
        values_sql = str(values_li).lstrip("[").rstrip("]")
        # replace into 将根据唯一索引，存在则删除后insert，不存在则直接insert
        sql = "REPLACE INTO {table_name} ({fields}) VALUES ({values});".format(
            table_name='cmdb_{type}'.format(type=type),  # 具体更新那张表
            fields=fields_sql,
            values=values_sql,
        )
        print(sql)
        c = exec_sql(sql)
        if c == 1:
            insert_count += 1
        elif c == 2:
            change_count += 1

    print("导入结果：新增{}行，更新{}行，忽略{}行".format(insert_count, change_count, ignore_count))
    res = {'code': 200, 'insert_count': insert_count, 'change_count': change_count, 'ignore_count': ignore_count}
    return jsonify(res)


# 新增，上传导入云资源接口
@bp.route('/api/v1/cmdb/cloud_resource_update', methods=['POST'])
def cloud_resource_update():
    type = request.form.get('type')  # type字段决定了具体更新到那张表(cmdb_%s)
    filename = request.form.get('filename')
    sheet_name = request.form.get('sheet_name')
    print("开始更新cmdb：", type, filename, sheet_name)

    # 开始使用excel更新数据库
    work_book = xlrd.open_workbook(filename)
    sheet = work_book.sheet_by_name(sheet_name)

    # 写死field字段
    th_li = ['org', 'project', 'res_type', 'instance_id', 'ip_private', 'ip_public', 'cpu', 'mem', 'sys_disk',
             'data_disk', 'system_version', 'res_function', 'comment', 'cpu_use_percent', 'mem_use_percent',
             'disk_use_percent',
             'oss_use', 'expire_time']
    fields_sql = str(th_li).lstrip("[").rstrip("]").replace('\'', '`')  # 表头转换为sql中的field字段

    # 读取表数据行（跳过表投，从第2行开始）
    tr_li = []
    for i in range(1, sheet.nrows):
        tr_li.append(sheet.row_values(i))

    # 初始化导入结果变量
    insert_count = 0
    change_count = 0

    # 遍历每一行数据，i为行，j为列，生成sql语句并执行
    for i in range(0, len(tr_li)):
        print(tr_li[i])
        if not tr_li[i][3]:
            # 跳过没有instance_id的行
            continue
        # 转换cpu mem sysdisk datadisk为int，以免sql无法插入
        tr_li[i][6] = str(int(tr_li[i][6])) if tr_li[i][6] else ''
        tr_li[i][7] = str(int(tr_li[i][7])) if tr_li[i][7] else ''
        tr_li[i][8] = str(int(tr_li[i][8])) if tr_li[i][8] else ''
        tr_li[i][9] = str(int(tr_li[i][9])) if tr_li[i][9] else ''
        tr_li[i][17] = str(int(tr_li[i][17])) if tr_li[i][17] else ''  # 调整日期显示

        values_li = []
        for j in range(0, len(th_li)):
            values_li.append(tr_li[i][j])
        # 组装sql
        values_sql = str(values_li).lstrip("[").rstrip("]")
        # replace into 将根据唯一索引，存在则删除后insert，不存在则直接insert
        sql = "REPLACE INTO {table_name} ({fields}) VALUES ({values});".format(
            table_name='cmdb_{type}'.format(type=type),  # 具体更新那张表
            fields=fields_sql,
            values=values_sql,
        )
        print(sql)
        c = exec_sql(sql)
        if c == 1:
            insert_count += 1
        elif c == 2:
            change_count += 1

    # update_expire_time_sql = "UPDATE cmdb_cloud_resource  set expire_time = NUll WHERE expire_time = 0"
    # exec_sql(update_expire_time_sql)
    print("导入结果：新增{}行，更新{}行".format(insert_count, change_count))
    res = {'code': 200, 'insert_count': insert_count, 'change_count': change_count, 'ignore_count': 0}
    return jsonify(res)


# 通用删除接口
@bp.route('/api/v1/cmdb/delete', methods=['POST'])
def delete():
    from datetime import datetime
    data = json.loads(request.get_data(as_text=True))  # 获取前端POST传过来的json数据
    print(data)
    table = "cmdb_" + data['type']
    if not data['ins_ids']:
        res = {"error": "参数为空"}
        return Response(status=403, mimetype="application/json", response=json.dumps(res))
    for ins_id in data['ins_ids']:
        delete_sql = "UPDATE {} SET is_deleted = 1,delete_time='{}' WHERE instance_id = '{}'".format(table,
                                                                                                     datetime.now(),
                                                                                                     ins_id)
        exec_sql(delete_sql)
    res = {'code': 200, 'delete_count': len(data['ins_ids'])}
    return jsonify(res)


if __name__ == '__main__':
    update('./upload/20220414175602_扶晓_云仰_阿里云资源-2022.xlsx', 'ECS')
